﻿<%@ WebHandler Language="C#" Class="MfgorderContainerFinishInfoDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;

/**
 * Description: 月度生产作业计划看板(按批次统计)
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.8.22
 **/

public class MfgorderContainerFinishInfoDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    UserImage u = new UserImage();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//页面表格数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportData":
                        exportExcel(jObject);//导出
                        break;
                    case "getMonthData":
                        r = getMonthData(jObject);//月--完成情况
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getThisMonthNotFinishPieData":
                        r = getThisMonthNotFinishPieData(jObject);//月--未完成情况饼图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getThisMonthNotFinishChartDetails":
                        r = getThisMonthNotFinishChartDetails(jObject);//本月月未完成订单明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportThisMonthNotFinishChartDetails":
                        exportThisMonthNotFinishChartDetails(jObject);//本月未完成订单明细--导出
                        break;
                    case "getWeekData":
                        r = getWeekData(jObject);//本周完成情况饼图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getWeekDataDetail":
                        r = getWeekDataDetail(jObject);//本周完成情况明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportThisWeekDetail":
                        exportThisWeekDetail(jObject);//本周完成明细--导出
                        break;
                    case "getTodayData":
                        r = getTodayData(jObject);//今日完成情况
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getTodayDetail":
                        r = getTodayDetail(jObject);//今日完成明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportTodayDetail":
                        exportTodayDetail(jObject);//今日完成明细--导出
                        break;
                    case "getStepPlanRateLine":
                        r = getStepPlanRateLine(jObject);//排产完成率折线图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getHistoryOrderChart":
                        r = getHistoryOrderChart(jObject);//各分厂遗留订单统计--饼图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getHistoryOrderDetails":
                        r = getHistoryOrderDetails(jObject);//各分厂遗留订单统计--查询明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportHistoryOrderDetails":
                        exportHistoryOrderDetails(jObject);//各分厂遗留订单统计--导出
                        break;
                    case "updateContainerFinishInfo":
                        r = updateContainerFinishInfo(jObject);//更新批次完成进度
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    string getSearchSqlString(JObject jObject)
    {
        string sql = @"
SELECT * 
FROM (
SELECT m.mfgorderid,m.mfgordername,m.qty mfgorderqty,cci.familylistnames,f.factoryname,w.workcentername fromworkcentername,
       cci.containerid,cci.containername,cci.qty,cci.workflowstepname,
       m.plannedstartdate,
       m.plannedcompletiondate,
       pb.productname,
       p.description productdesc,
       t.spectotal,t.specfinish,
       t.worktimetotal,t.worktimefinish,
       CASE WHEN NVL(t.spectotal,0) <> 0 THEN 
            (CASE WHEN ROUND(NVL(t.specfinish,0) / NVL(t.spectotal,0),4) * 100 >= 100 THEN 100 
                  ELSE ROUND(NVL(t.specfinish,0) / NVL(t.spectotal,0),4) * 100 
            END) ELSE 0 
       END specRate,
       CASE WHEN NVL(t.worktimetotal,0) <> 0 THEN 
            (CASE WHEN ROUND(NVL(t.worktimefinish,0) / NVL(t.worktimetotal,0),4) * 100 >= 100 THEN 100 
                  ELSE ROUND(NVL(t.worktimefinish,0) / NVL(t.worktimetotal,0),4) * 100 
            END) ELSE 0 
       END worktimeRate,
       CASE WHEN NVL(t.specfinish,0) >= t.spectotal THEN '已完成'
            --WHEN NVL(t.specfinish,0) < t.spectotal AND cci.isselfmfg = 0 AND cci.materialdate IS NULL THEN '未到料'
            WHEN NVL(t.specfinish,0) < t.spectotal AND TRUNC(SYSDATE) > TRUNC(m.plannedcompletiondate) THEN '超期未完'
            WHEN NVL(t.specfinish,0) < t.spectotal AND TRUNC(SYSDATE) <= TRUNC(m.plannedcompletiondate) THEN '未完成' 
       END status,
       cci.isselfmfg,cci.materialdate,t.teamname
FROM mfgorder m
LEFT JOIN productbase pb1 ON pb1.productbaseid = m.productbaseid
LEFT JOIN product p ON p.productid = nvl(pb1.revofrcdid,m.productid)
LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
LEFT JOIN mes_factory_mapping f ON f.factoryid= m.factoryid
LEFT JOIN containercurrentinfo cci ON cci.mfgorderid = m.mfgorderid
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN orderstatus od ON od.orderstatusid = m.orderstatusid
LEFT JOIN mesreport_container_transfer t ON t.containerid = cci.containerid
LEFT JOIN workcenter w ON w.workcenterid = cci.fromworkcenterid
LEFT JOIN team t ON t.teamid = cci.teamid
WHERE NVL(od.orderstatusname,'打开') <> '关闭' AND f.factorycode <> '603'
      AND cci.containerstatus <> 2 AND c.rbtogetherflag IS NULL AND cci.workflowroute IS NULL ";
        //分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "全部")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "全部")
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        //工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND cci.fromworkcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        //当前班组
        if (jObject["teamid"] != null && jObject["teamid"].ToString() != "")
        {
            sql += string.Format(" AND cci.teamid = '{0}'", jObject["teamid"].ToString());
        }

        //件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND pb.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND m.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //日期范围
        if (jObject["selecteddate"] != null && jObject["selecteddate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selecteddate"].ToString());
            sql += string.Format(" AND m.plannedcompletiondate >= TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
            sql += string.Format(" AND m.plannedcompletiondate <= TO_DATE('{0}','yyyy-mm-dd')", arr[1].ToString());
        }

        if (jObject["status"] != null && jObject["status"].ToString() != "全部")
        {
            if (jObject["status"].ToString() == "已完成")
            {
                sql += " AND NVL(t.specfinish,0) >= t.spectotal ";
            }
            else if (jObject["status"].ToString() == "未完成")
            {
                sql += " AND NVL(t.specfinish,0) < t.spectotal";
            }
            else if (jObject["status"].ToString() == "未到料")
            {
                sql += " AND NVL(t.specfinish,0) < t.spectotal AND cci.isselfmfg = 0 AND cci.materialdate IS NULL ";
            }
            else if (jObject["status"].ToString() == "已到料未完成")
            {
                sql += " AND NVL(t.specfinish,0) < t.spectotal AND ((cci.isselfmfg = 0 AND cci.materialdate IS NOT NULL) OR (cci.isselfmfg = 1)) ";
            }
        }

        sql += @"
) temp 
WHERE temp.status IS NOT NULL 
ORDER BY temp.mfgordername,temp.containername";
        return sql;
    }

    //导出前处理表格
    DataTable setExportDataTable(DataTable dtResult)
    {
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        dtResult.Columns.Remove("mfgorderid");
        dtResult.Columns.Remove("spectotal");
        dtResult.Columns.Remove("specfinish");
        dtResult.Columns.Remove("worktimetotal");
        dtResult.Columns.Remove("worktimefinish");
        dtResult.Columns.Remove("isselfmfg");
        dtResult.Columns.Remove("materialdate");
        dtResult.Columns.Remove("containerid");

        dtResult.Columns["mfgordername"].Caption = "订单号";
        dtResult.Columns["mfgorderqty"].Caption = "订单数量";
        dtResult.Columns["familylistnames"].Caption = "型号";
        dtResult.Columns["factoryname"].Caption = "主制分厂";
        dtResult.Columns["fromworkcentername"].Caption = "主制工区";
        dtResult.Columns["containername"].Caption = "批次号";
        dtResult.Columns["qty"].Caption = "批次数量";
        dtResult.Columns["workflowstepname"].Caption = "当前工序";
        dtResult.Columns["plannedstartdate"].Caption = "计划开始";
        dtResult.Columns["plannedcompletiondate"].Caption = "计划完成";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["productdesc"].Caption = "零件名称";
        dtResult.Columns["specrate"].Caption = "工序完成百分比";
        dtResult.Columns["worktimerate"].Caption = "工时完成百分比";
        dtResult.Columns["status"].Caption = "状态";
        dtResult.Columns["teamname"].Caption = "当前班组";
        return dtResult;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        dtResult = setExportDataTable(dtResult);
        ExportHelper.ExportExcel(dtResult, "月度生产作业计划看板导出数据");
    }

    //月度完成情况
    Result getMonthData(JObject jObject)
    {
        string sql = @"SELECT SUM(DECODE(t.status,'已完成',1,0)) finished,SUM(DECODE(t.status,'已完成',0,1)) notfinished FROM ( {0} ) t ";
        sql = string.Format(sql, getSearchSqlString(jObject));
        DataTable dt = dal.GetDataTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //月--未完成情况饼图
    Result getThisMonthNotFinishPieData(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        DataTable dtResult = new DataTable();
        dtResult.Columns.Add("wdl");//未到料
        dtResult.Columns.Add("ydlwwc");//已到料未完成

        DataRow dr = dtResult.NewRow();
        dr["wdl"] = dt.Select("isselfmfg = 0 AND materialdate IS NULL ", "").Length;
        dr["ydlwwc"] = dt.Select("(isselfmfg = 0 AND materialdate IS NOT NULL) OR (isselfmfg = 1)", "").Length;

        dtResult.Rows.Add(dr);

        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //本月未完成订单明细
    Result getThisMonthNotFinishChartDetails(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出本月未完成订单明细
    void exportThisMonthNotFinishChartDetails(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        dtResult = setExportDataTable(dtResult);
        ExportHelper.ExportExcel(dtResult, jObject["title"].ToString());
    }

    //本周完成情况
    Result getWeekData(JObject jObject)
    {
        string sql = @" SELECT SUM(DECODE(t.status,'已完成',1,0)) finished,SUM(DECODE(t.status,'已完成',0,1)) notfinished FROM ( {0} ) t ";
        sql = string.Format(sql, getSearchSqlString(jObject));
        DataTable dt = dal.GetDataTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //本周完成情况明细
    Result getWeekDataDetail(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    // 本周完成情况明细导出
    void exportThisWeekDetail(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        dtResult = setExportDataTable(dtResult);
        ExportHelper.ExportExcel(dtResult, "本周" + jObject["status"].ToString() + "明细");
    }

    string getTodayDataSql(JObject jObject)
    {
        string sql = @"
SELECT f.factoryname,cci.fromworkcentername,cci.mfgordername,cci.containername,ws.sequence,ws.workflowstepname,
       csp.plannedfinishdate plandate,t.checkDate,DECODE(t.checkDate,NULL,'未完工','已完工') status
FROM containerstepplan csp
LEFT JOIN containercurrentinfo cci ON cci.containerid = csp.containerid
LEFT JOIN mes_factory_mapping f ON f.factoryid = cci.originalfactoryid
LEFT JOIN workflowstep ws ON ws.workflowstepid = csp.workflowstepid
LEFT JOIN (
    SELECT csr.containerid,csr.workflowstepid,MAX(csr.checkdate) checkDate
    FROM containersteprecord csr
    GROUP BY csr.containerid,csr.workflowstepid 
) t ON t.containerid = cci.containerid AND t.workflowstepid = ws.workflowstepid
LEFT JOIN containerstepnoship csn ON csn.containerid = cci.containerid AND csn.workflowstepid = ws.workflowstepid AND csn.noship = 2 AND csn.isuse = 0
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN containercommidetail cm ON cm.containerid = csp.containerid AND cm.workflowstepid = csp.workflowstepid AND cm.isuse = 0
WHERE csp.plantype = 1 AND cci.containerstatus = 1
      AND ((cci.isinstore = 1 AND cci.workflowid <> ws.workflowid) OR (cci.isinstore = 0 AND cci.workflowid = ws.workflowid))
      AND NVL(s.controlcode,'LY01') <> 'LY05' AND cm.containercommidetailid IS NULL AND csn.containerstepnoshipid IS NULL";//过滤LY05 临时外委 工序不加工

        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "全部")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
        }
        else
        {
            sql += " AND f.factorycode IN ('600','601','607','608','609')";
        }

        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND cci.fromworkcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        if (jObject["teamid"] != null && jObject["teamid"].ToString() != "")
        {
            sql += string.Format(" AND cci.teamid = '{0}'", jObject["teamid"].ToString());
        }

        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND cci.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        if (jObject["today"] != null && jObject["today"].ToString() != "")
        {
            sql += string.Format(" AND TRUNC(csp.plannedfinishdate) >= TO_DATE('{0}','yyyy-mm-dd')", jObject["today"].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedfinishdate) <= TO_DATE('{0}','yyyy-mm-dd')", jObject["today"].ToString());
        }

        if (jObject["iscomplete"] != null)
        {
            if (jObject["iscomplete"].ToString() == "已完工")
            {
                sql += " AND t.checkDate IS NOT NULL";
            }
            else
            {
                sql += " AND t.checkDate IS NULL";
            }
        }

        return sql;
    }

    //今日完成情况
    Result getTodayData(JObject jObject)
    {
        string sql = @"
SELECT to_char(t.plandate,'yyyy-MM-dd'),COUNT(t.workflowstepname) planqty,SUM(DECODE(t.status,'已完工',1,0)) finished,
       COUNT(t.workflowstepname) - SUM(DECODE(t.status,'已完工',1,0)) notfinished
FROM (
  {0}
) t
GROUP BY to_char(t.plandate,'yyyy-MM-dd')";
        sql = string.Format(sql, getTodayDataSql(jObject));
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    // 今日完成明细
    Result getTodayDetail(JObject jObject)
    {
        string sql = getTodayDataSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    // 今日完成情况明细导出
    void exportTodayDetail(JObject jObject)
    {
        string sql = getTodayDataSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns["factoryname"].Caption = "主制分厂";
        dtResult.Columns["fromworkcentername"].Caption = "主制工区";
        dtResult.Columns["mfgordername"].Caption = "订单号";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["sequence"].Caption = "工序号";
        dtResult.Columns["workflowstepname"].Caption = "排产工序";
        dtResult.Columns["plandate"].Caption = "计划完成时间";
        dtResult.Columns["checkdate"].Caption = "实际完成时间";
        dtResult.Columns["status"].Caption = "完工状态";
        ExportHelper.ExportExcel(dtResult, "今日" + jObject["iscomplete"].ToString() + "明细");
    }

    //排产完成率折线图
    Result getStepPlanRateLine(JObject jObject)
    {
        string sql = @"
SELECT to_char(t.plandate,'mm-dd') plandate,COUNT(t.workflowstepname) planqty,SUM(DECODE(t.status,'已完工',1,0)) finishqty,
       ROUND(SUM(DECODE(t.status,'已完工',1,0)) / COUNT(t.workflowstepname) ,4) * 100 finishrate
FROM (
  SELECT f.factoryname,cci.fromworkcentername,cci.mfgordername,cci.containername,ws.sequence,ws.workflowstepname,
         csp.plannedfinishdate plandate,t.checkDate,DECODE(t.checkDate,NULL,'未完工','已完工') status
  FROM containerstepplan csp
  LEFT JOIN containercurrentinfo cci ON cci.containerid = csp.containerid
  LEFT JOIN mes_factory_mapping f ON f.factoryid = cci.originalfactoryid
  LEFT JOIN workflowstep ws ON ws.workflowstepid = csp.workflowstepid
  LEFT JOIN (
      SELECT csr.containerid,csr.workflowstepid,MAX(csr.checkdate) checkDate
      FROM containersteprecord csr
      GROUP BY csr.containerid,csr.workflowstepid 
  ) t ON t.containerid = cci.containerid AND t.workflowstepid = ws.workflowstepid
  LEFT JOIN containerstepnoship csn ON csn.containerid = cci.containerid AND csn.workflowstepid = csp.workflowstepid AND csn.noship = 2 AND csn.isuse = 0
  LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
  LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
  LEFT JOIN containercommidetail cm ON cm.containerid = csp.containerid AND cm.workflowstepid = csp.workflowstepid AND cm.isuse = 0
  WHERE csp.plantype = 1 AND cci.containerstatus = 1
        AND ((cci.isinstore = 1 AND cci.workflowid <> ws.workflowid) OR (cci.isinstore = 0 AND cci.workflowid = ws.workflowid))
        AND NVL(s.controlcode,'LY01') <> 'LY05' AND cm.containercommidetailid IS NULL AND csn.containerstepnoshipid IS NULL";

        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "全部")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }
        else
        {
            sql += " AND f.factorycode IN ('600','601','607','608','609')";
        }

        //日期范围
        if (jObject["thismonth"] != null && jObject["thismonth"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["thismonth"].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedfinishdate) >= TO_DATE('{0}','yyyy-mm-dd') ", arr[0].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedfinishdate) <= TO_DATE('{0}','yyyy-mm-dd') ", arr[1].ToString());
        }

        sql += @"
) t
GROUP BY t.plandate
ORDER BY t.plandate";
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //各分厂遗留订单公用查询sql
    string getHistoryOrderSql(JObject jObject)
    {
        string sql = @"
SELECT * FROM (
SELECT m.mfgorderid,m.mfgordername,m.qty mfgorderqty,cci.familylistnames,f.factoryname,cci.fromworkcentername,
       cci.containerid,cci.containername,cci.qty,cci.workflowstepname,
       m.plannedstartdate,
       m.plannedcompletiondate,
       pb.productname,
       p.description productdesc,
       t.spectotal,t.specfinish,
       t.worktimetotal,t.worktimefinish,
       CASE WHEN NVL(t.spectotal,0) <> 0 THEN 
            (CASE WHEN ROUND(NVL(t.specfinish,0) / NVL(t.spectotal,0),4) * 100 >= 100 THEN 100 
                  ELSE ROUND(NVL(t.specfinish,0) / NVL(t.spectotal,0),4) * 100 
            END) ELSE 0 
       END specRate,
       CASE WHEN NVL(t.worktimetotal,0) <> 0 THEN 
            (CASE WHEN ROUND(NVL(t.worktimefinish,0) / NVL(t.worktimetotal,0),4) * 100 >= 100 THEN 100 
                  ELSE ROUND(NVL(t.worktimefinish,0) / NVL(t.worktimetotal,0),4) * 100 
            END) ELSE 0 
       END worktimeRate,
       CASE WHEN NVL(t.specfinish,0) >= t.spectotal THEN '已完成'
            WHEN NVL(t.specfinish,0) < t.spectotal AND cci.isselfmfg = 0 AND cci.materialdate IS NULL THEN '未到料'
            WHEN NVL(t.specfinish,0) < t.spectotal AND TRUNC(SYSDATE) > TRUNC(m.plannedcompletiondate) THEN '超期未完'
            WHEN NVL(t.specfinish,0) < t.spectotal AND TRUNC(SYSDATE) <= TRUNC(m.plannedcompletiondate) THEN '未完成' 
       END status,
       cci.isselfmfg,cci.materialdate
FROM mfgorder m
LEFT JOIN productbase pb1 ON pb1.productbaseid = m.productbaseid
LEFT JOIN product p ON p.productid = nvl(pb1.revofrcdid,m.productid)
LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
LEFT JOIN mes_factory_mapping f ON f.factoryid= m.factoryid
LEFT JOIN containercurrentinfo cci ON cci.mfgorderid = m.mfgorderid
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN orderstatus od ON od.orderstatusid = m.orderstatusid
LEFT JOIN Userattribute u ON u.parentid = f.factoryid AND u.userattributename = '分厂代码'
LEFT JOIN mesreport_container_transfer t ON t.containerid = cci.containerid
WHERE NVL(od.orderstatusname,'打开') <> '关闭' AND NVL(u.attributevalue,'空') <> '603' 
      AND cci.containerstatus <> 2 AND c.rbtogetherflag IS NULL AND cci.workflowroute IS NULL 
      AND NVL(t.specfinish,0) < t.spectotal";
        //分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "全部")
        {
            sql += string.Format(" AND f.factoryid = '{0}'", jObject["factoryid"].ToString());
        }

        if (jObject["factoryname"] != null)
        {
            sql += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        //工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND cci.fromworkcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        //件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND pb.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //订单号
        if (jObject["mfgordername"] != null && jObject["mfgordername"].ToString() != "")
        {
            sql += string.Format(" AND m.mfgordername = '{0}'", jObject["mfgordername"].ToString());
        }

        //遗留订单时间范围
        if (jObject["historyOrderDate"] != null && jObject["historyOrderDate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["historyOrderDate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND TRUNC(m.plannedcompletiondate) BETWEEN TO_DATE('{0}','yyyy-mm-dd') and TO_DATE('{1}','yyyy-mm-dd')", arr[0].ToString(), arr[1].ToString());
            }
        }
        else
        {
            JArray arr = JArray.Parse(jObject["thismonth"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND TRUNC(m.plannedcompletiondate) < TO_DATE('{0}','yyyy-mm-dd')", arr[0].ToString());
            }
        }

        sql += @") temp 
WHERE temp.status IS NOT NULL";
        return sql;
    }

    //历史遗留订单统计（饼图）
    Result getHistoryOrderChart(JObject jObject)
    {
        string sql = @"SELECT t.factoryname,count(1) qty FROM ({0}) t GROUP BY t.factoryname";
        sql = string.Format(sql, getHistoryOrderSql(jObject));
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //历史遗留订单（饼图明细）
    Result getHistoryOrderDetails(JObject jObject)
    {
        string sql = getHistoryOrderSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //历史遗留订单（导出）
    void exportHistoryOrderDetails(JObject jObject)
    {
        string sql = getHistoryOrderSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        dtResult = setExportDataTable(dtResult);
        ExportHelper.ExportExcel(dtResult, jObject["title"].ToString());
    }

    // 更新批次完成进度
    Result updateContainerFinishInfo(JObject jObject)
    {
        string containerid = jObject["containerid"].ToString();
        string specTotal = @"
SELECT cci.mfgorderid,cci.containerid,COUNT(ws.workflowstepid) specTotal,SUM(NVL(sc.durationperunit,0)) worktimeTotal
FROM mesreport_container_transfer t
LEFT JOIN containercurrentinfo cci ON cci.containerid = t.containerid
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN workflow wf ON wf.workflowid = 
     DECODE(cci.isinstore,0,cci.workflowid,NVL((CASE WHEN cci.bsworkflowid IN ('0005aa8000003eef','0005aa80000023af','0005aa80000036bd') THEN '' ELSE cci.bsworkflowid END),c.mfgworkflowid))
LEFT JOIN workflowstep ws ON ws.workflowid = wf.workflowid
LEFT JOIN stepschedulingdetail sc ON sc.stepid= ws.workflowstepid
WHERE ws.workflowstepid <> ws.workflowstepname AND ws.wipmsglabel IS NULL AND NVL(ws.controlcode,'LY01') <> 'LY07' AND t.containerid = '{0}'
GROUP BY cci.mfgorderid,cci.containerid";
        DataTable dtSpecTotal = dal.GetDataTable(string.Format(specTotal, containerid));

        string worktimeSql = @"
SELECT t.containerid,SUM(T.workTime) worktimefinish 
FROM ( 
        --查询外委工时(批次未入库)
        SELECT cm.containerid,SUM(nvl(st.durationperunit,0)) workTime
        FROM containercommissioning cm
        LEFT JOIN containercurrentinfo cci ON cci.containerid = cm.containerid
        LEFT JOIN workflowstep ws ON ws.workflowstepid = cm.workflowstepid
        LEFT JOIN Workflowstep ws2 ON ws2.workflowstepid = cm.inworkflowstepid
        LEFT JOIN workflow wf ON wf.workflowid = ws.workflowid
        LEFT JOIN workflowstep wss ON wss.workflowid = wf.workflowid
        LEFT JOIN stepschedulingdetail st ON st.stepid = wss.workflowstepid
        WHERE cm.indate IS NOT NULL 
              AND wss.sequence >= ws.sequence 
              AND wss.sequence < ws2.sequence 
              AND cci.containerid = '{0}'
        GROUP BY cm.containerid
        --查询转工工时      
       UNION ALL 
       SELECT M.containerid,SUM(M.workTime) workTime
       FROM (
             SELECT cci.containerid,cr.containershiprecordid,SUM(nvl(st.durationperunit,0)) workTime
             FROM containershiprecive cr
             LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
             LEFT JOIN containershiprecordstep csd ON csd.containershiprecordid = cr.containershiprecordid
             LEFT JOIN workflowstep ws ON csd.workflowstepid = ws.workflowstepid
             LEFT JOIN stepschedulingdetail st on st.stepid = ws.workflowstepid
             INNER JOIN mesreport_container_transfer t ON t.containerid = cci.containerid
             WHERE cr.reciver IS NOT NULL AND cr.isuse = 0 
                    AND ws.wipmsglabel IS NULL AND NVL(ws.controlcode,'0') <> 'LY07' 
                    AND cci.containerid = '{0}'
             GROUP BY cci.containerid,cr.containershiprecordid
       ) M 
       GROUP BY M.containerid
       --查询检验工时（在containersteprecord内排除掉那些在外协过程中产生的报工检验记录）
       UNION ALL 
       SELECT M.containerid,SUM(nvl(st.durationperunit,0)) workTime 
       FROM (
             SELECT DISTINCT csr.containerid,csr.workflowstepid
             FROM containercurrentinfo cci
             LEFT JOIN containersteprecord csr ON csr.containerid = cci.containerid
             LEFT JOIN sessionvalues sv ON sv.employeeid = csr.checkemployeeid
             INNER JOIN mesreport_container_transfer t ON t.containerid = cci.containerid
             LEFT JOIN workflowstep ws ON ws.workflowstepid = csr.workflowstepid
             WHERE csr.usecompleted = 1 AND csr.checkemployeeid IS NOT NULL
                   AND csr.factoryid = cci.originalfactoryid 
                   AND ws.wipmsglabel IS NULL AND NVL(ws.controlcode,'0') <> 'LY07'
                   AND cci.containerid = '{0}'
       ) M
       LEFT JOIN stepschedulingdetail st on st.stepid = M.workflowstepid
       GROUP BY M.containerid
) T 
GROUP BY T.containerid";
        DataTable dtWorktime = dal.GetDataTable(string.Format(worktimeSql, containerid));

        string specSql = @"
SELECT t.containerid,SUM(t.specQty) specfinish 
FROM ( 
       SELECT cm.containerid,(ws2.sequence - ws.sequence) specQty
       FROM containercommissioning cm
       LEFT JOIN containercurrentinfo cci ON cci.containerid = cm.containerid
       LEFT JOIN workflowstep ws ON ws.workflowstepid = cm.workflowstepid
       LEFT JOIN workflowstep ws2 ON ws2.workflowstepid = cm.inworkflowstepid
       INNER JOIN mesreport_container_transfer t ON t.containerid = cci.containerid
       WHERE cm.indate IS NOT NULL AND cci.containerid = '{0}'
       
       UNION ALL
        
       SELECT M.containerid,SUM(M.specQty) specQty
       FROM (
             SELECT cci.containerid,cr.containershiprecordid,(MAX(ws.sequence) - MIN(ws.sequence))+1 specQty
             FROM containershiprecive cr
             LEFT JOIN containershiprecordstep csd ON csd.containershiprecordid = cr.containershiprecordid
             LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
             LEFT JOIN workflowstep ws ON csd.workflowstepid = ws.workflowstepid
             INNER JOIN mesreport_container_transfer t ON t.containerid = cci.containerid
             WHERE cr.recivedate IS NOT NULL AND cr.isuse = 0 AND ws.wipmsglabel IS NULL AND NVL(ws.controlcode,'0') <> 'LY07'
                   AND cci.containerid = '{0}'
             GROUP BY cci.containerid,cr.containershiprecordid
       ) M 
       GROUP BY M.containerid
       
       UNION ALL 
       SELECT csr.containerid,COUNT(DISTINCT csr.workflowstepname) specQty
       FROM containercurrentinfo cci
       LEFT JOIN containersteprecord csr ON csr.containerid = cci.containerid
       LEFT JOIN sessionvalues sv ON sv.employeeid = csr.checkemployeeid
       INNER JOIN mesreport_container_transfer t ON t.containerid = cci.containerid
       LEFT JOIN workflowstep ws ON ws.workflowstepid = csr.workflowstepid
       WHERE csr.usecompleted = 1 AND csr.checkemployeeid IS NOT NULL AND csr.factoryid = cci.originalfactoryid
             AND cci.containerid = '{0}' AND ws.wipmsglabel IS NULL AND NVL(ws.controlcode,'0') <> 'LY07'
       GROUP BY csr.containerid
) t GROUP BY t.containerid";
        DataTable dtSpec = dal.GetDataTable(string.Format(specSql, containerid));

        string isinstoreSql = @"
SELECT cci.isinstore,t.spectotal,t.specfinish
FROM mesreport_container_transfer t
LEFT JOIN containercurrentinfo cci ON cci.containerid = t.containerid
WHERE cci.containerid = '{0}'";
        DataTable dtContainerInstore = dal.GetDataTable(string.Format(isinstoreSql, containerid));

        if (dtSpecTotal.Rows.Count > 0 && dtSpec.Rows.Count > 0 && dtWorktime.Rows.Count > 0)
        {
            string specfinish = "";
            if (dtContainerInstore.Rows[0]["isinstore"].ToString() == "1")
            {
                specfinish = dtContainerInstore.Rows[0]["isinstore"].ToString();
            }
            else
            {
                specfinish = dtSpec.Rows[0]["specfinish"].ToString();
            }

            string updateSql = string.Format("update mesreport_container_transfer t SET t.specfinish = '{0}',t.worktimefinish = '{1}',t.spectotal = '{2}',t.worktimetotal='{3}' WHERE t.containerid = '{4}'",
                specfinish,//已完成工序
                dtWorktime.Rows[0]["worktimefinish"].ToString(),//已完成工时
                dtSpecTotal.Rows[0]["specTotal"].ToString(),//总工序数
                dtSpecTotal.Rows[0]["worktimeTotal"].ToString(),//总工时
                containerid);
            dal.ExecuteSql(updateSql);
        }

        string sql = @"
SELECT CASE WHEN NVL(t.spectotal,0) <> 0 THEN ROUND(NVL(t.specfinish,0) / NVL(t.spectotal,0),4) * 100 ELSE 0 END specRate,
       CASE WHEN NVL(t.worktimetotal,0) <> 0 THEN 
            (CASE WHEN ROUND(NVL(t.worktimefinish,0) / NVL(t.worktimetotal,0),4) * 100 >= 100 THEN 100 
                  ELSE ROUND(NVL(t.worktimefinish,0) / NVL(t.worktimetotal,0),4) * 100 
            END) ELSE 0 
       END worktimeRate,
       CASE WHEN NVL(t.specfinish,0) >= t.spectotal THEN '已完成'
            WHEN NVL(t.specfinish,0) < t.spectotal AND cci.isselfmfg = 0 AND cci.materialdate IS NULL THEN '未到料'
            WHEN NVL(t.specfinish,0) < t.spectotal AND TRUNC(SYSDATE) > TRUNC(m.plannedcompletiondate) THEN '超期未完'
            WHEN NVL(t.specfinish,0) < t.spectotal AND TRUNC(SYSDATE) <= TRUNC(m.plannedcompletiondate) THEN '未完成' 
       END status,
       t.containerid 
FROM mesreport_container_transfer t
LEFT JOIN mfgorder m ON m.mfgorderid = t.mfgorderid
LEFT JOIN containercurrentinfo cci ON cci.containerid = t.containerid
WHERE t.containerid = '{0}'
";
        sql = string.Format(sql, containerid);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}